import pymysql


def create_table():
    db = pymysql.connect(host='localhost', user='root',
                         db='test', port=3306,
                         passwd='root', charset='utf8')

    cursor = db.cursor()
    cursor.execute('drop table if exists students;')
    sql = """create table students(
    id int primary key auto_increment,
    name varchar(20),
    age int unsigned);"""
    try:
        cursor.execute(sql)
        print('数据表创建success')
    except Exception as e:
        print('数据库创建失败的原因：%s' % e)
    finally:
        cursor.close()
        db.close()


def insert_table():
    db = pymysql.connect(host='localhost', user='root',
                         db='test', port=3306,
                         passwd='root', charset='utf8')
    cursor = db.cursor()
    sql = """insert into students(name,age)values('小明', 22),
    ('小花', 19),
    ('小平', 21),
    ('小蛋', 23);
    """
    try:
        cursor.execute(sql)
        db.commit()
        print('insert success ')
    except Exception as e:
        print('flase:%s' % e)
        db.rollback()
    finally:
        cursor.close()
        db.close()


# create_table()
insert_table()
